from django.core.management.base import BaseCommand, CommandError
from django.utils.dateparse import parse_datetime
from django.utils.timezone import make_aware
from oplata.models import *
import openpyxl


def set_auto_now_fields(enable:bool, *mymodels):
    """Turns off and on the auto_now and auto_now_add attributes on a Model's fields,
    so that an instance of the Model can be saved with a custom value.
    """
    for model in mymodels:
        for field in model._meta.local_fields:
            if hasattr(field, 'auto_now'):
                field.auto_now = enable
            if hasattr(field, 'auto_now_add'):
                field.auto_now_add = enable


def replace_None_with_None(input):
    """ Заменяет текст 'None' настоящим None"""
    if input is None or input == 'None':
        return None
    return input


def replace_None_with_Blank(input):
    """ Заменяет текст 'None' пустой строкой"""
    if input is None or input == 'None':
        return ''
    return input


class Command(BaseCommand):
    help = "Загружает старые данные из exel файла, игнорируя уже существующие объекты"

    def add_arguments(self, parser):
        parser.add_argument("filename")

    def handle(self, *args, **options):
        workbook = openpyxl.load_workbook(options['filename'], read_only=True)
        worksheet = workbook.active
        rows = worksheet.iter_rows()
        headers = [str(cell.value) for cell in next(rows)]
        print(headers)
        # временно отключим auto_now поля что бы заполнить
        set_auto_now_fields(False, Client, Order, LeadSource)
        # читаем позиционно
        row_n=2
        # большой цикл по объектам
        for row in rows:
            v = [str(cell.value) for cell in row]
            # сначала смотрим основные объекты не связанные с другими

            ######################## Product #########################
            product = None
            try:
                product = Product.objects.get(name=v[5])
            except Product.DoesNotExist:
                print(f"Не найден продукт: {v[5]} в строке {row_n} id={v[0]} ")
                break

            ######################## Client #########################
            client = None
            try:
                client = Client.objects.get(email=replace_None_with_Blank(v[9]))
            except Client.DoesNotExist:
                client = Client.objects.create(
                    email=replace_None_with_Blank(v[9]),
                    name=replace_None_with_Blank(v[7]),
                    surname=replace_None_with_Blank(v[8]),
                    phone=replace_None_with_Blank(v[10]),
                    created=make_aware(parse_datetime(replace_None_with_Blank(v[1]))), # добавили timezone
                    updated=make_aware(parse_datetime(replace_None_with_Blank(v[2])))
                )
                client.save()

            ######################## PaymentGate #########################
            payment_gate = None
            if v[21] != 'None':
                try:
                    payment_gate = PaymentGate.objects.get(name=replace_None_with_Blank(v[21]))
                except PaymentGate.DoesNotExist:
                    print(f"Не найден PaymentGate: {v[21]} в строке {row_n} id={v[0]} ")
                    break

            # print(client, product, payment_gate)

            ######################## Order #########################
            order = None
            try:
                order = Order.objects.get(pk=v[0])
            except Order.DoesNotExist:
                order = Order.objects.create(
                    pk=int(v[0]),
                    product=product,
                    client=client,
                    name=replace_None_with_Blank(v[7]),
                    surname=replace_None_with_Blank(v[8]),
                    phone=replace_None_with_Blank(v[10]),
                    payment_gate=payment_gate,
                    price=replace_None_with_Blank(v[3]),
                    status=Order.ORDER_STATUS_INVERSE[replace_None_with_Blank(v[14])],
                    profit=0.0 if v[19] == 'None' else v[19],
                    client_comment=replace_None_with_Blank(v[6]),
                    seller_comment=replace_None_with_Blank(v[23]),
                    promocode=replace_None_with_Blank(v[4]),
                    created=make_aware(parse_datetime(replace_None_with_Blank(v[1]))),
                    updated=make_aware(parse_datetime(replace_None_with_Blank(v[2])))
                )
                order.save()

            ######################## LeadSource #########################
            lead_source = None
            if v[24] != 'None':
                print("Lead ", v[24], v[24]=='None')
                try:
                    lead_source = LeadSource.objects.get(order=order)
                except LeadSource.DoesNotExist:
                    # site?fbclid=PAAaabWG_AmvsxbGFDFNNcZzXdC1Sfl4S0HXSdAlRMAhW7a6aLPQRDioSUzoA_aem_th_AQ5MEaprYmYF0Lwkmdxzo6e6_BogAwhpXEx408IuLuWPHeWeh9dXf04Pf-gJITBL_8E
                    utm_campaign = replace_None_with_None(v[28])
                    fbclid = ''
                    if utm_campaign is not None and '?fbclid=' in utm_campaign: # найдем facebook lead id
                        utm_campaign, fbclid = utm_campaign.split('?fbclid=')

                    lead_source = LeadSource.objects.create(
                        order=order,
                        utm_source=v[24],
                        utm_medium=replace_None_with_None(v[25]),
                        utm_campaign=utm_campaign,
                        utm_term=replace_None_with_None(v[26]),
                        utm_content=replace_None_with_None(v[27]),
                        fbclid=fbclid,
                        created=make_aware(parse_datetime(replace_None_with_Blank(v[1])))
                    )
                    lead_source.save()
                    print(str(lead_source))

            print(row_n, v[1])
            row_n=row_n+1
            # SQL statement ALTER TABLE tbl AUTO_INCREMENT = 100;

        set_auto_now_fields(True, Client, Order, LeadSource)